//These are functions that interface with our database

#include "database.h"

//this function is for inserting into the usage_stats table of the database
int insert_usage_stats(int lockerNumber) {
    printf("insert_usage stats in\n");
    sqlite3 *conn;
    int error = 0;
    const char *errMSG;
    const char temp[100];
    printf("sprintf is a liar\n");
    sprintf(temp, "INSERT INTO usage_stats (lockerNumber, lockTime) VALUES (%d, DateTime(\'now\'))", lockerNumber);
    printf("sprintf done: %s\n,",temp);
    error = sqlite3_open("/home/pi/projects/NFC_Smart_Lockers/smartlocker_db.db", &conn);
    if(error) {
        printf("Can not open database. error: %d\n",error);
        return -1;
    }
    printf("connection made\n");

    error = sqlite3_exec(conn,
                         temp,
                         0, 0, &errMSG);

    if(error) {
        printf("Can not insert : %d\n", error);
        printf(errMSG);
        printf("\n");
        return -1;
    }

    sqlite3_close(conn);

    return 0;
}

//this function is for updating the usage_stats table of the database with lock time and if it was a managment unlock or not
int update_usage_stats(int adminUnlock, int lockerNumber) {
    printf("updating usage_stats\n");
    sqlite3 *conn;
    int error = 0;
    const char *errMSG;
    const char temp[150];
    sprintf(temp, "UPDATE usage_stats SET unlockTime = DateTime(\'now\'), adminUnlock = %d WHERE lockerNumber = %d AND (unlockTime IS NULL OR unlockTime = \'\')", adminUnlock, lockerNumber);

    error = sqlite3_open("/home/pi/projects/NFC_Smart_Lockers/smartlocker_db.db", &conn);
    if(error) {
        printf("Can not open database. error: %d\n",error);
        return -1;
    }

    printf(temp);

    error = sqlite3_exec(conn,
                         temp,
                         0, 0, &errMSG);

    if(error) {
        printf("Can not update : %d\n", error);
        printf(errMSG);
        printf("\n");
        return -1;
    }

    sqlite3_close(conn);

    return 0;
}

//this function is for inserting into the key_values table
int insert_key_values(int lockerNumber, char key[]) {
    sqlite3 *conn;
    int error = 0;
    const char *errMSG;
    const char temp[100];
    sprintf(temp, "INSERT INTO key_values (lockerNumber, key) VALUES (%d, \'%s\')", lockerNumber, key);

    error = sqlite3_open("/home/pi/projects/NFC_Smart_Lockers/smartlocker_db.db", &conn);
    if(error) {
        printf("Can not open database. error: %d\n",error);
        return -1;
    }

    error = sqlite3_exec(conn,
                         temp,
                         0, 0, 0);

    if(error) {
        printf("Can not insert : %d\n", error);
        return -1;
    }

    sqlite3_close(conn);

    return 0;
}

//this function is for deleting a record from the key_values table
int delete_key_values(int lockerNumber) {
    sqlite3 *conn;
    int error = 0;
    const char *errMSG;
    const char temp[100];
    sprintf(temp, "DELETE FROM key_values WHERE lockerNumber = %d", lockerNumber);

    error = sqlite3_open("/home/pi/projects/NFC_Smart_Lockers/smartlocker_db.db", &conn);
    if(error) {
        printf("Can not open database. error: %d\n",error);
        return -1;
    }

    error = sqlite3_exec(conn,
                         temp,
                         0, 0, 0);

    if(error) {
        printf("Can not insert : %d\n", error);
        return -1;
    }

    sqlite3_close(conn);

    return 0;
}

//this function is for selecting all the records from the key values table
/* void select_key_values(struct key_values *key_values) {
 *     sqlite3 *conn;
 *     int error = 0;
 *     sqlite3_stat *res;
 *     const char *errMSG;
 *     const char temp[100];
 *     sprintf(temp, "SELECT * FROM key_values", lockerNumber);
 *
 *     error = sqlite3_open("/home/pi/projects/NFC_Smart_Lockers/smartlocker_db.db", &conn);
 *     if(error) {
 *         printf("Can not open database. error: %d\n",error);
 *         return -1;
 *     }
 *
 *     error = sqlite3_exec(conn,
 *                          temp,
 *                          1000, &res, &errMSG);
 *
 *     if(error) {
 *         printf("Can not insert : %d\n", error);
 *         return -1;
 *     }
 *
 *     struct key_values temp;
 *     int i = 0;
 *
 *     while (sqlite3_step(res) == SQLITE_ROW) {
 *         temp.lockerNumber = atoi(sqlite3_column_text(res,0));
 *         temp.key = sqlite3_column_text(res, 1);
 *         key_values[i] = temp;
 *         i++;
 *     }
 *
 *     sqlite3_finalize(res);
 *
 *     sqlite3_close(conn);
 *
 *     return 0;
 * }
 */
